package com.wjf.shortlink.shortlink.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.wjf.shortlink.shortlink.dao.entity.LinkAccessLogsDAO;
import com.wjf.shortlink.shortlink.dao.entity.LinkAccessStatsDAO;
import com.wjf.shortlink.shortlink.dto.req.ShortLinkGroupStatsAccessRecordReqDTO;
import com.wjf.shortlink.shortlink.dto.req.ShortLinkGroupStatsReqDTO;
import com.wjf.shortlink.shortlink.dto.req.ShortLinkStatsReqDTO;
import com.wjf.shortlink.shortlink.dto.resp.ShortLinkStatsRespDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

//短链接访问日志持久层
public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogsDAO> {

    //获取短链接指定日期内的pv，uv，uip的数据
    @Select("SELECT " +
            "      COUNT(tlal.user) AS pv, " +
            "      COUNT(DISTINCT tlal.user) AS uv," +
            "      COUNT(DISTINCT tlal.ip) AS uip " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "     tlal.full_short_url = #{param.fullShortUrl}" +
            "     AND tl.gid = #{param.gid} " +
            "     AND tl.del_flag = '0' " +
            "     AND tl.enable_status = #{param.enableStatus} " +
            "     AND tlal.create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tlal.full_short_url,tl.gid;"
    )
    LinkAccessStatsDAO findPvUvUipStatsByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);

    //获取短链接指定日期内访问高频的ip统计
    @Select("SELECT " +
            "      tlal.ip, " +
            "      COUNT(tlal.ip) AS count, " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "     tlal.full_short_url = #{param.fullShortUrl}" +
            "     AND tl.gid = #{param.gid} " +
            "     AND tl.del_flag = '0' " +
            "     AND tl.enable_status = #{param.enableStatus} " +
            "     AND tlal.create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tlal.full_short_url,tl.gid,tlal.ip " +
            "ORDER BY " +
            "     count DESC " +
            "LIMIT 5;"
    )
    List<HashMap<String, Object>> listTopIpByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);

    //获取短链接指定日期内的新旧日期访客
    @Select("SELECT " +
            "      SUM(old_user) AS oldUserCnt, " +
            "      SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "     SELECT " +
            "          CASE WHEN COUNT(DISTINCT DATE(tlal.create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "          CASE WHEN COUNT(DISTINCT DATE(tlal.create_time)) = 1 AND MAX(tlal.create_time) >= #{param.startDate} AND MAX(tlal.create_time) <= #{param.endDate} THEN 1 ELSE 0 END AS AS new_user " +
            "     FROM " +
            "         link tl INNER JOIN " +
            "         link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "     WHERE " +
            "        tlal.full_short_url = #{param.fullShortUrl} " +
            "        AND tl.gid = #{param.gid} " +
            "        AND tl.enable_status = #{param.enableStatus} " +
            "        AND tl.del_flag = '0' " +
            "     GROUP BY " +
            "         tlal.user " +
            ") AS user_counts;"
    )
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO shortLinkStatsReqDTO);

    //根据分组获取指定日期内的pv uv UIP数据
    @Select("SELECT " +
            "     COUNT(tlal.user) AS pv, " +
            "     COUNT(DISTINCT tlal.user) AS uv, " +
            "     COUNT(DISTINCT tlal.ip) AS uip " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tl.gid;"
    )
    LinkAccessStatsDAO findPvUvUipStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO shortLinkGroupStatsReqDTO);

    //根据分组获取指定日期内高频ip访问数据
    @Select("SELECT " +
            "     tlal.ip, " +
            "     COUNT(tlal.ip) AS count " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "GROUP BY " +
            "     tl.gid,tlal.ip " +
            "ORDER BY " +
            "     count DESC " +
            "LIMIT 5;"
    )
    List<HashMap<String, Object>> listTopIpByGroup(@Param("param") ShortLinkGroupStatsReqDTO shortLinkGroupStatsReqDTO);

    //获取用户信息是否新老访客
    @Select("<script> " +
            "SELECT " +
            "     tlal.user, " +
            "     CASE " +
            "        WHEN MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate} THEN '新用户' " +
            "        ELSE '老访客' " +
            "     END AS uvType " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "     tlal.full_short_url = #{fullShortUrl} " +
            "     AND tl.gid = #{gid} " +
            "     AND tl.del_flag = '0' " +
            "     AND tl.enable_status = #{enableStatus} " +
            "     AND tlal.user IN " +
            "     <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'>" +
            "      #{item}" +
            "     </foreach> " +
            "GROUP BY " +
            "    tlal.user;" +
            " </script>")
    List<Map<String, Object>> selectUvTypeByUsers(
            @Param("gid") String gid,
            @Param("fullShortUrl") String fullShortUrl,
            @Param("enableStatus") Integer enableStatus,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );

    //根据分组获取短链接监控分页数据
    @Select("SELECT " +
            "     tlal.* " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} AND #{param.endDate} " +
            "ORDER BY " +
            "      tlal.create_time DESC;"
    )
    IPage<LinkAccessLogsDAO> selectGroupPage(@Param("param") ShortLinkGroupStatsAccessRecordReqDTO reqDTO);

    //根据分组获取用户信息是否新老访客
    @Select("<script> " +
            "SELECT " +
            "     tlal.user, " +
            "     CASE " +
            "        WHEN MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate} THEN '新用户' " +
            "        ELSE '老访客' " +
            "     END AS uvType " +
            "FROM " +
            "    link tl INNER JOIN " +
            "    link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "     tl.gid = #{gid} " +
            "     AND tl.del_flag = '0' " +
            "     AND tl.enable_status = '0' " +
            "     AND tlal.user IN " +
            "     <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'>" +
            "      #{item}" +
            "     </foreach> " +
            "GROUP BY " +
            "    tlal.user;" +
            " </script>")
    List<Map<String, Object>> selectGroupUvTypeByUsers(
            @Param("gid") String gid,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );
}
